use RD4_DWCR;
go
select COUNT_BIG(*) from JDEPYEmployeeTransactionHist

Select  DISTINCT(JAB.AlphaName),  Sum(AmountGrossPay) over (order by  JAB.addressnumber) , HomebusinessUnit, DENSE_RANK() over (partition by HomeBusinessUnit order by AmountGrossPay)
from 
	JDEPYEmployeeTransactionDetl as ETD
Join JDEABAddressBookMaster as JAB
	on ETD.AddressNumber=JAB.AddressNumber

WITH CTE (Alphaname, SumGrossPay, HomeBusinessUnit)
as(
Select  
	DISTINCT(AlphaName),  
	Sum(AmountGrossPay) over (order by  addressnumber) as sumGross, 
	HomeBusinessUniT
from 
	JDEPYEmployeeTransactionDetl

)

Select  
	AlphaName, 
	SumGrossPay,
	HomeBusinessUnit,
	DENSE_RANK() over (partition by HomeBusinessUnit order by SumGrossPay ) as [Rank in BU]
from 
	CTE

